﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace YunShop_WebAPI.BLL
{
    public class DBHelper
    {
        /// <summary>
        /// 连接字符串
        /// </summary>
        //public static string connstr = System.Configuration.ConfigurationManager.ConnectionStrings["Connection"].ConnectionString;
        public static string ConnMaster = System.Configuration.ConfigurationManager.ConnectionStrings["Connection"].ConnectionString;
        public static string HostMaster = System.Configuration.ConfigurationManager.AppSettings["HostMaster"].ToString();
        public static string ConnCluster = System.Configuration.ConfigurationManager.ConnectionStrings["ConnCluster"].ConnectionString;
        public static string ConnMasterSplit = System.Configuration.ConfigurationManager.ConnectionStrings["ConnMasterSplit"].ConnectionString;
        public static bool DebugFlag = CommonFun.ConvertToBoolean(System.Configuration.ConfigurationManager.AppSettings["DebugFlag"]);//是主调试

        public static ArrayList DataTable2ArrayListNotNull(DataTable data)
        {
            ArrayList array = new ArrayList();
            for (int i = 0; i < data.Rows.Count; i++)
            {
                DataRow row = data.Rows[i];

                Hashtable record = new Hashtable();
                for (int j = 0; j < data.Columns.Count; j++)
                {
                    object cellValue = row[j];
                    if (cellValue.GetType() == typeof(DBNull))
                    {
                        Type dataType = data.Columns[j].DataType;
                        if (dataType.Name == "String")
                        {
                            cellValue = "";
                        }
                        else if ((dataType.Name == "Int32") || (dataType.Name == "Int64") || (dataType.Name == "int")
                            || (dataType.Name == "Int16") || (dataType.Name == "Decimal") || (dataType.Name == "Boolean"))
                        {
                            cellValue = "0";
                        }
                        else
                        {
                            cellValue = null;
                        }
                    }
                    record[data.Columns[j].ColumnName] = cellValue;
                }
                array.Add(record);
            }
            return array;
        }


        public static ArrayList DataTable2ArrayList(DataTable data)
        {
            ArrayList array = new ArrayList();
            for (int i = 0; i < data.Rows.Count; i++)
            {
                DataRow row = data.Rows[i];

                Hashtable record = new Hashtable();
                for (int j = 0; j < data.Columns.Count; j++)
                {
                    object cellValue = row[j];
                    if (cellValue.GetType() == typeof(DBNull))
                    {
                        cellValue = null;
                    }
                    record[data.Columns[j].ColumnName] = cellValue;
                }
                array.Add(record);
            }
            return array;
        }

        public static DataTable GetPDSTable(string SP, SqlParameter[] param, string connstr)
        {
            SqlConnection Conn = new SqlConnection(connstr);
            SqlCommand Cmd = new SqlCommand(SP, Conn);
            Cmd.CommandType = CommandType.StoredProcedure;
            foreach (var p in param)
            {
                Cmd.Parameters.Add(p.ParameterName, p.SqlDbType).Value = p.SqlValue;
            }
            SqlDataAdapter DApter = new SqlDataAdapter(Cmd);
            DataSet dt = new DataSet();
            DApter.Fill(dt);
            DApter.Dispose();
            Conn.Dispose();
            Conn.Close();
            return dt.Tables[0];
        }

        public static ArrayList Select(string sql,string connstr)
        {
            DataTable data = new DataTable();
            SqlConnection con = new SqlConnection(connstr);
            SqlCommand cmd = new SqlCommand(sql, (SqlConnection)con);
            cmd.CommandTimeout = 300;
            SqlDataAdapter adapter = new SqlDataAdapter(sql, connstr);
            adapter.Fill(data);
            return DataTable2ArrayList(data);
        }

        public static ArrayList Select(string sql, SqlParameter[] args, string connstr)
        {
            DataTable data = new DataTable();
            SqlConnection con = new SqlConnection(connstr);
            SqlCommand cmd = new SqlCommand(sql, (SqlConnection)con);
            cmd.Parameters.AddRange(args);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            adapter.Fill(data);
            cmd.Parameters.Clear();
            return DataTable2ArrayList(data);
        }

        public static ArrayList Select(string sql, Hashtable args, string connstr)
        {
            DataTable data = new DataTable();
            SqlConnection con = new SqlConnection(connstr);
            SqlCommand cmd = new SqlCommand(sql, (SqlConnection)con);
            if (args != null) SetArgs(sql, args, cmd);

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            adapter.Fill(data);

            return DataTable2ArrayList(data);
        }


        /// <summary>
        /// 从参数化查询获取完整SQL
        /// 2016-01-11 Jonson
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static string GetSelectParaSQL(string sql, SqlParameter[] args, string connstr)
        {
            DataTable data = new DataTable();
            SqlConnection con = new SqlConnection(connstr);
            SqlCommand cmd = new SqlCommand(sql, (SqlConnection)con);
            cmd.Parameters.AddRange(args);

            return GetCommandText(cmd.CommandText, cmd.Parameters);
        }

        /// <summary>
        /// 从参数化查询获取完整SQL
        /// 2016-01-11 Jonson
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static string GetSelectParaSQL(string sql, Hashtable args, string connstr)
        {
            DataTable data = new DataTable();
            SqlConnection con = new SqlConnection(connstr);
            SqlCommand cmd = new SqlCommand(sql, (SqlConnection)con);
            if (args != null) SetArgs(sql, args, cmd);

            return GetCommandText(cmd.CommandText, cmd.Parameters);
        }

        /// <summary>
        /// 从参数化查询获取完整SQL
        /// 2016-01-11 Jonson
        /// </summary>
        /// <param name="commandtext"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        static string GetCommandText(string commandtext, SqlParameterCollection parameters)
        {
            string sValue = "";
            foreach (SqlParameter parameter in parameters)
            {
                sValue = "'" + (parameter.Value is String ? parameter.Value.ToString() : parameter.Value) + "'";
                commandtext = commandtext.Replace(parameter.ParameterName, sValue);
            }

            return commandtext;
        }

        /// <summary>
        /// 从参数化查询获取完整SQL
        /// 2016-12-09 Jonson
        /// </summary>
        /// <param name="commandtext"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static string GetCommandSqlText(string commandtext, SqlParameter[] parm)
        {
            string sValue = "";
            foreach (SqlParameter parameter in parm)
            {
                sValue = "'" + parameter.Value.ToString() + "'";
                commandtext = commandtext.Replace(parameter.ParameterName, sValue);
            }
            return commandtext;
        }

        public static void Execute(string sql, Hashtable args, string connstr)
        {
            SqlConnection con = new SqlConnection(connstr);
            try
            {
                SqlCommand cmd = new SqlCommand(sql, con);
                if (args != null) SetArgs(sql, args, cmd);
                con.Open();
                cmd.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
                con = null;
            }
        }

        public static void AddParas(string key, object value, IDbCommand cmd)
        {
            if (!cmd.Parameters.Contains(key))
            {
                cmd.Parameters.Add(new SqlParameter(key, value));
            }
        }

        public static void SetArgs(string sql, Hashtable args, IDbCommand cmd)
        {
            MatchCollection ms = Regex.Matches(sql, @"@\w+");
            foreach (Match m in ms)
            {
                string key = m.Value;

                Object value = args[key];
                if (value == null)
                {
                    value = args[key.Substring(1)];
                }
                if (value == null) value = DBNull.Value;
                if (!cmd.Parameters.Contains(key))
                {
                    cmd.Parameters.Add(new SqlParameter(key, value));
                }
            }
            cmd.CommandText = sql;
        }

        public static void SetArgs(string sql, Hashtable args, IDbCommand cmd, List<string> exParameters)
        {
            MatchCollection ms = Regex.Matches(sql, @"@\w+");
            foreach (Match m in ms)
            {
                string key = m.Value;

                Object value = args[key];
                if (value == null)
                {
                    value = args[key.Substring(1)];
                }
                if (value == null) value = DBNull.Value;
                if (!cmd.Parameters.Contains(key) && !exParameters.Contains(key))
                {
                    cmd.Parameters.Add(new SqlParameter(key, value));
                }
            }
            cmd.CommandText = sql;
        }

        public static DataSet GetDs(string strSQL, string connstr)
        {
            return SqlHelper.ExecuteDataset(connstr, CommandType.Text, strSQL);
        }

        public static DataTable GetDataTable(string strSQL, string connstr)
        {
            return SqlHelper.ExecuteDataset(connstr, CommandType.Text, strSQL).Tables[0];
        }

        public static DataTable GetDataTable(string strSQL, SqlParameter[] parm, string connstr)
        {
            return SqlHelper.ExecuteDataset(connstr, CommandType.Text, strSQL, parm).Tables[0];
            //return SqlHelper.ExecuteDataset(GetConStr(), CommandType.Text, strSQL).Tables[0];
        }

        public static DataSet GetDataSet(string strSQL, SqlParameter[] parm, string connstr)
        {
            return SqlHelper.ExecuteDataset(connstr, CommandType.Text, strSQL, parm);
        }

        public static object GetValue(string strSQL, string connstr)
        {
            return SqlHelper.ExecuteScalar(connstr, CommandType.Text, strSQL);
        }

        public static bool GetExistsValue(string strSQL, SqlParameter[] parm, string connstr)
        {
            DataTable dt = SqlHelper.ExecuteDataset(connstr, CommandType.Text, strSQL, parm).Tables[0];
            if (dt.Rows.Count == 0)
            {
                return false;
            }
            return true;
        }

        /// <summary>
        /// 执行无返回结果ＳＱＬ语句
        /// </summary>
        /// <param name="strCon"></param>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public static string ExeSQL(string strSQL, string connstr)
        {
            SqlConnection sqlCon = new SqlConnection(connstr);
            SqlCommand myCommand = new SqlCommand(strSQL, sqlCon);
            myCommand.CommandTimeout = 0;
            try
            {
                try
                {
                    sqlCon.Open();
                    myCommand.ExecuteNonQuery();
                    sqlCon.Close();
                    return "";
                }
                catch (Exception ex)
                {
                    return ex.Message;
                }
            }
            finally
            {
                myCommand.Dispose();
                sqlCon.Dispose();
            }
        }


        /// <summary>
        /// 执行无返回结果ＳＱＬ语句
        /// </summary>
        /// <param name="strCon"></param>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public static string ExeSQL(string strSQL, SqlParameter[] parm, string connstr)
        {
            SqlConnection sqlCon = new SqlConnection(connstr);
            SqlCommand myCommand = new SqlCommand(strSQL, sqlCon);
            myCommand.CommandTimeout = 0;
            try
            {
                try
                {
                    sqlCon.Open();
                    myCommand.Parameters.AddRange(parm);
                    myCommand.ExecuteNonQuery();
                    sqlCon.Close();
                    return "";
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            finally
            {
                myCommand.Dispose();
                sqlCon.Dispose();
            }
        }

        public static string SaveData(DataTable dt, string strSQL, string connstr)
        {
            try
            {
                SqlConnection con = new SqlConnection(connstr);

                SqlDataAdapter myAdt = new SqlDataAdapter(strSQL, con);
                SqlCommandBuilder bd = new SqlCommandBuilder(myAdt);

                myAdt.Update(dt);
                con.Dispose();
                myAdt.Dispose();
                bd.Dispose();
                return "";
            }
            catch (SqlException ex)
            {
                if (ex.Number == 2627)
                    return "数据重复，请检查!!";
                else
                    return ex.Message;
            }
        }

        public static string SaveData(DataSet ds, Hashtable htParm, string connstr)
        {
            string sSQL;
            int iCnt = 0;
            SqlConnection con = new SqlConnection(connstr);
            con.Open();
            SqlTransaction tran = con.BeginTransaction();
            try
            {
                iCnt = htParm.Count;

                for (int i = 1; i <= iCnt; i++)
                {
                    sSQL = htParm[i.ToString()].ToString();

                    DataTable dt = ds.Tables[i.ToString()];

                    SqlCommand myComm = new SqlCommand();
                    myComm.Connection = con;
                    myComm.Transaction = tran;
                    myComm.CommandText = sSQL;

                    myComm.CommandTimeout = 0;

                    SqlDataAdapter myAdt = new SqlDataAdapter();
                    myAdt.SelectCommand = myComm;

                    SqlCommandBuilder bd = new SqlCommandBuilder(myAdt);
                    myAdt.Update(dt);
                }
                tran.Commit();
                return "";
            }
            catch (SqlException ex)
            {
                tran.Rollback();
                if (ex.Number == 2627)
                    return "数据重复，请检查!!";
                else
                    return ex.Message;
            }
        }

        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandTimeout = 600;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {
                foreach (SqlParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }

        /// <summary>
        /// 执行多条语句返回受影响的记录
        /// </summary>
        /// <param name="SQLStringList"></param>
        public static int ExecuteSqlTran(System.Collections.Generic.List<CommandInfo> cmdList, string connstr)
        {
            string msql_Log = "";
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        int count = 0;
                        //癹伴
                        foreach (CommandInfo myDE in cmdList)
                        {
                            string cmdText = myDE.CommandText;
                            msql_Log = cmdText;
                            SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);

                            if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
                            {
                                if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
                                {
                                    trans.Rollback();
                                    return 0;
                                }

                                object obj = cmd.ExecuteScalar();
                                bool isHave = false;
                                if (obj == null && obj == DBNull.Value)
                                {
                                    isHave = false;
                                }
                                isHave = Convert.ToInt32(obj) > 0;

                                if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
                                {
                                    trans.Rollback();
                                    return 0;
                                }
                                if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
                                {
                                    trans.Rollback();
                                    return 0;
                                }
                                continue;
                            }
                            int val = cmd.ExecuteNonQuery();
                            count += val;
                            if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
                            {
                                trans.Rollback();
                                return 0;
                            }
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                        return count;
                    }
                    catch (Exception e)
                    {                        
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }

        public class CommandInfo
        {
            public object ShareObject = null;
            public object OriginalData = null;
            event EventHandler _solicitationEvent;
            public event EventHandler SolicitationEvent
            {
                add
                {
                    _solicitationEvent += value;
                }
                remove
                {
                    _solicitationEvent -= value;
                }
            }
            public void OnSolicitationEvent()
            {
                if (_solicitationEvent != null)
                {
                    _solicitationEvent(this, new EventArgs());
                }
            }
            public string CommandText;
            public System.Data.Common.DbParameter[] Parameters;
            public EffentNextType EffentNextType = EffentNextType.None;
            public CommandInfo()
            {

            }
            public CommandInfo(string sqlText, SqlParameter[] para)
            {
                this.CommandText = sqlText;
                this.Parameters = para;
            }
            public CommandInfo(string sqlText, SqlParameter[] para, EffentNextType type)
            {
                this.CommandText = sqlText;
                this.Parameters = para;
                this.EffentNextType = type;
            }
        }
        public enum EffentNextType
        {
            /// <summary>
            /// 对其他语句无任何影响 
            /// </summary>
            None,
            /// <summary>
            /// 当前语句必须为"select count(1) from .."格式，如果存在则继续执行，不存在回滚事务
            /// </summary>
            WhenHaveContine,
            /// <summary>
            /// 当前语句必须为"select count(1) from .."格式，如果不存在则继续执行，存在回滚事务
            /// </summary>
            WhenNoHaveContine,
            /// <summary>
            /// 当前语句影响到的行数必须大于0，否则回滚事务
            /// </summary>
            ExcuteEffectRows,
            /// <summary>
            /// 引发事件-当前语句必须为"select count(1) from .."格式，如果不存在则继续执行，存在回滚事务
            /// </summary>
            SolicitationEvent
        }

        /// <summary>
        /// 将数据表导入数据库表
        /// </summary>
        /// <param name="strTableName"></param>
        /// <param name="dtData"></param>
        public static void SqlBulkCopyInsert(string strTableName, DataTable dtData, string connstr)
        {
            try
            {
                using (SqlBulkCopy sqlRevdBulkCopy = new SqlBulkCopy(connstr))           //引用SqlBulkCopy  
                {
                    sqlRevdBulkCopy.DestinationTableName = strTableName;                //数据库中对应的表名  
                    sqlRevdBulkCopy.NotifyAfter = dtData.Rows.Count;                    //有几行数据  
                    sqlRevdBulkCopy.WriteToServer(dtData);                              //数据导入数据库  
                    sqlRevdBulkCopy.Close();                                            //关闭连接  
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 批量执行SQL语句
        /// </summary>
        /// <param name="sqls">sql语句</param>
        /// <param name="m_param">参数化</param>
        /// <returns></returns>
        public static bool BatchExecuteBySql(object[] StbSql, object[] param, string connstr)
        {

            try
            {
                using (SqlConnection connection = new SqlConnection(connstr))
                {
                    connection.Open();
                    SqlTransaction dbTransaction = connection.BeginTransaction();
                    try
                    {
                        for (int i = 0; i < StbSql.Length; i++)
                        {
                            StringBuilder builder = (StringBuilder)StbSql[i];
                            SqlCommand myCommand = new SqlCommand(builder.ToString(), connection, dbTransaction);
                            if (builder != null)
                            {
                                myCommand.CommandTimeout = 0;
                                SqlParameter[] paramArray = (SqlParameter[])(param[i]);
                                myCommand.Parameters.AddRange(paramArray);
                                myCommand.ExecuteNonQuery();

                            }
                        }
                        dbTransaction.Commit();
                        return true;
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine(e.Message);
                        dbTransaction.Rollback();
                        //DbLog.WriteException(e);
                    }
                    finally
                    {
                        connection.Close();
                        connection.Dispose();
                        dbTransaction.Dispose();
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                Console.WriteLine(e.Message);
            }
            return false;
        }


    }
}
